package com.adam.springbucks.config;

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.FilterEventAdapter;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.alibaba.druid.proxy.jdbc.ResultSetProxy;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.h2.visitor.H2SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;

import javax.servlet.annotation.WebFilter;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

@Slf4j
@WebFilter(urlPatterns = "/*",filterName = "bucksDruidSQLFilter")
@Configuration
public class BucksDruidSQLFilter extends FilterEventAdapter {

    @Override
    public void connection_connectBefore(FilterChain chain, Properties info) {
        log.info("BEFORE CONNECTION!");
    }

    @Override
    public void connection_connectAfter(ConnectionProxy connection) {
        log.info("AFTER CONNECTION!");
    }

    public ResultSetProxy preparedStatement_executeQuery(FilterChain chain, PreparedStatementProxy statement)
            throws SQLException {
        log.info("preparedStatement_executeQuery");
        try {
            List<SQLStatement> stmtList = SQLUtils.parseStatements(statement.getSql(), JdbcConstants.H2);
            if (stmtList.size() > 1) {
                throw new SQLException(String.format("SQL语句不能超过去 1 条，具体 SQL:%s", stmtList.toString()));
            } else {
                H2SchemaStatVisitor sv = new H2SchemaStatVisitor();
                for (SQLStatement sqlStatement : stmtList) {
                    sqlStatement.accept(sv);
                    for (TableStat.Condition condition : sv.getConditions()) {
                        // 判断是否包含IN及IN中包含值的个数
                        if ("in".equalsIgnoreCase(condition.getOperator()) && condition.getValues().size() > 10) {
                            throw new SQLException(String.format("查询参数包含in,in条件值不能超过 10 个，具体 SQL:%s", sqlStatement));
                        }
                    }

                    if (!sqlStatement.toString().contains("?")) {// 不包含？占位符
                        throw new SQLException(String.format("过滤器检测该SQL为拼接语句，请使用参数，具体 SQL:%s", sqlStatement));
                    }
                }
            }

        } catch (SQLException e) {
            log.error("ConnectionLogFilter 执行 SQL 校验不通过", e);
            return null;
        } catch (Exception e) {
            log.error("ConnectionLogFilter 执行 SQL 校验失败", e);
            return null;
        }

        return chain.preparedStatement_executeQuery(statement);
    }
}
